//
//  EFBCompanyChartDB.m
//  Pods
//
//  Created by E.F.B on 15-5-26.
//
//

#import "EFBCompanyChartDB.h"
#import "EFBChartDataBaseUtil.h"
#import "ChartContext.h"

@implementation EFBCompanyChartDB

+ (NSArray *)getAllCompanyChartsInfo
{
    __block NSMutableArray *array = [NSMutableArray array];
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getChartDatabase];
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
             NSLog(@"Could not open DB.");
             return ;
         }
         
         // kind of experimentalish.
         [db setShouldCacheStatements:YES];
         
         if ( ![db tableExists:@"companyCharts_info"] )
         {
             return ;
         }
         FMResultSet *dataRs = nil;
         dataRs = [db executeQuery:[NSString stringWithFormat:@"select * from %@",@"companyCharts_info"]];
         
         while ([dataRs next])
         {
             [array addObject:[dataRs resultDictionary] ];
         }
         
         [dataRs close];
         [db close];
     }];
    return array;
}

+ (NSMutableArray *)getTheCompanyChartsByCode4:(NSString *)code4
{
    __block NSMutableArray *array = [NSMutableArray array];
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getChartDatabase];
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
             NSLog(@"Could not open DB.");
             return ;
         }
         
         // kind of experimentalish.
         [db setShouldCacheStatements:YES];
         
         if ( ![db tableExists:@"companyCharts_info"] )
         {
             return ;
         }
         FMResultSet *dataRs = nil;
         if (code4){
             dataRs = [db executeQuery:@"select * from companyCharts_info where code4 = ?",code4];
//             dataRs = [db executeQuery:@"select * from companyCharts_info"];
         }
         
         while ([dataRs next])
         {
             [array addObject:[dataRs resultDictionary] ];
         }
         
         [dataRs close];
         [db close];
     }];
    return array;
}

+ (NSMutableArray *)getTheCompantChartsByCode4:(NSString *)code4 andSubsidiary:(NSString *)subsidiary
{
    __block NSMutableArray *array = [NSMutableArray array];
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getChartDatabase];
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
             NSLog(@"Could not open DB.");
             return ;
         }
         
         // kind of experimentalish.
         [db setShouldCacheStatements:YES];
         
         if ( ![db tableExists:@"companyCharts_info"] )
         {
             return ;
         }
         FMResultSet *dataRs = nil;
         if (code4){
             dataRs = [db executeQuery:@"select * from companyCharts_info where code4 = ? and subsidiary = ?", code4, subsidiary];
//             dataRs = [db executeQuery:@"select * from companyCharts_info where subsidiary = ?", subsidiary];
         }
         
         while ([dataRs next])
         {
             [array addObject:[dataRs resultDictionary] ];
         }
         
         [dataRs close];
         [db close];
     }];
    return array;
}

//EOSID
+ (NSMutableArray *)getEOSIDForAirport:(NSString *)name catalog:(NSString *)catalog
{
    __block NSMutableArray *airportArray = [[[NSMutableArray alloc] init] autorelease];
    NSDateFormatter *dateFormat = [[[NSDateFormatter alloc] init] autorelease];
    [dateFormat setDateFormat:@"yyyy-MM-dd"];
    
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getChartDatabase];
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
             NSLog(@"Could not open DB.");
             return ;
         }
         [db setShouldCacheStatements:YES];
         if ( ![db tableExists:@"airport_sefp"] )
         {
             return ;
         }
         NSString *today = [NSString stringWithFormat:@"%@%@",[dateFormat stringFromDate:[NSDate date]],@"T00:00:00.000+08:00"];

         NSString *sql1 = [NSString stringWithFormat:@"select * from airport_sefp where airport_code = '%@' and sub_app_type = '%@' and (expiry_date >= '%@' or expiry_date = '' or expiry_date is null) order by name",name ,catalog,today];
         if (sql1 != nil)
         {
             FMResultSet *results = [db executeQuery:sql1];
             while ([results next])
             {
                 [airportArray addObject:[results resultDictionary]];
             }
             [results close];
         }
         
         [db close];
     }];
    
    return airportArray;
}

//other
+ (NSMutableArray *)getAirportForCOAndEOSID
{
    __block NSMutableArray *resultArray = [[[NSMutableArray alloc] init] autorelease];
    
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getChartDatabase];
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
             NSLog(@"Could not open DB.");
             return ;
         }
         [db setShouldCacheStatements:YES];
//         if ( ![db tableExists:@"airport_sefp"] && ![db tableExists:@"companyCharts_info"])
         if (![db tableExists:@"companyCharts_info"])
         {
             return ;
         }
//        FMResultSet *results = [db executeQuery:@"select airport_code from airport_sefp where sub_app_type = '单发程序' union select code4 from companyCharts_info order by airport_code"];
         FMResultSet *results = [db executeQuery:@"select code4 from companyCharts_info order by code4"];
         while ([results next])
         {
//            [resultArray addObject:[results stringForColumn:@"airport_code"]];
             [resultArray addObject:[results stringForColumn:@"code4"]];
         }
         [results close];
         [db close];
     }];

    return resultArray;
}

+ (NSString *)getCityNameFromIcao:(NSString *)icao
{
    __block NSString * name = icao;
    FMDatabaseQueue *dbQueue = [EFBChartDataBaseUtil getAirPortDatabase];
    [dbQueue inDatabase:^(FMDatabase *db) {
        if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
            return ;
        }
        [db setShouldCacheStatements:YES];
        if ( ![db tableExists:@"airports"] ) {
            return ;
        }
        
        FMResultSet *dataRs = [db executeQuery:@"select * from airports where airport_code4 = ?",icao];
        while ([dataRs next])
        {
            name = [dataRs stringForColumn:@"airport_name_zh"];
            if ([name isEqualToString:@""] || [name isEqualToString:@"null"] || [name isEqual:[NSNull null]]) {
                name = icao;
            }
        }
        [dataRs close];
        [db close];
    }];
    return name;
}

@end
